package view;

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.util.ArrayList;
import model.Region;
import model.Serpent;
import model.State;
import control.CConnection;
import control.CRegion;
import control.CSerpent;
import control.CState;


public class ExtractVenomousSerpentData {
	public static void main(String args[]) {
		
		CConnection cn = new CConnection();
		
		try {
			cn.createConnection();
			
			ResultSet rs = cn.getStatement().executeQuery("SELECT S.SerpentID AS sid, St.StateID AS stid FROM Serpent S, State St, Region R WHERE S.RegionID = R.RegionID AND R.StateID = St.StateID AND (S.Specie like 'Apostolepis coronata' OR S.Specie like 'Amnesteophis melanauchen' OR S.Specie like 'Anilius scytale' OR S.Specie like 'Apostolepis cearensis')");
			/*CSerpent cserpent = new CSerpent(cn);
			ArrayList<Serpent> serpents = cserpent.retrieveAll();
			
	        CRegion cregion = new CRegion(cn);
	        ArrayList<Region> regions = cregion.retrieveAll();
	
	        CState CState = new CState(cn);
			ArrayList<State> states = CState.retrieveAll();*/
	
	    	BufferedWriter out = new BufferedWriter(new FileWriter("felipedavi/venomous_serpent_state.txt"));
	
	    	/*for (int i = 0; i < serpents.size(); i++)
	        {
	    		for (int j = 0; j < states.size(); j++)
	    		{*/
	    			for (int k = 0; rs.next(); k++)
	    			{
	    				/*if (serpents.get(i).getRegionID() == regions.get(j).getRegionID() && regions.get(j).getStateID() == states.get(k).getId())
	    				{*/
	    					//Integer serpentid = serpents.get(i).getSerpentID();
	    					//Integer stateid   = states.get(j).getId();
	    		    		out.write(rs.getInt("sid") + " " + rs.getInt("stid"));
	    		    		out.newLine();
	    			}
	    			/*}
	    		}
	        }
*/	    	
			out.close();
			
			System.out.println("File generated successfully.");
		}
		catch (ClassNotFoundException e) {
			System.err.println("Couldn't connect to Derby Database.");
		}
		catch (SQLException e) {
			System.err.println("Error in SQL Statement.");
		} catch (IOException e) {
			System.err.println("Error in I/O.");
		}
	
	
	}
}